** Rankings for main analysis

/* input 
${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta
${output_data}quota_mex_weight_hs6.dta
*/

/* output
/* Used in main analysis */
${output_data}mexico_for_analysis_04_07_hs6_total.dta
${output_data}US_for_analysis_04_07_hs6_restrict.dta

/* Used in robustness checks */
${output_data}mexico_for_analysis_04_06_hs6_restrict.dta
${output_data}mexico_for_analysis_04_08_hs6_restrict.dta
${output_data}US_for_analysis_04_06_hs6_restrict.dta
${output_data}US_for_analysis_04_08_hs6_restrict.dta
*/

*************************
*Step 1 Create importer rank by hs6
*************************

* Create data for making rankings in 2004
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep rfc hs6 year newclid2  value_exporter_importer
keep if year==2004
label var rfc "exporter id"
label var newclid2 "importer id"
label var hs6 "hs6"
label var value_exporter_importer "exports of hs6 between exporter and importer"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6)
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+importer_id
gen hs6_exporter_id=hs6+"_"+rfc
save "${temp_data}data_for_ranking_hs6.dta", replace

* Identify partner with highest match value
use "${temp_data}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer newclid2
by hs6: gen mv_rank=_n
keep hs6_importer_id hs6_exporter_id mv_rank 
tostring mv_rank, generate(mv_rank_s)
gen hs6_importer_id_mv_rank= hs6_importer_id+"_"+mv_rank_s
keep hs6_exporter_id hs6_importer_id_mv_rank
sort hs6_importer_id_mv_rank
rename hs6_exporter_id mv_partner
label var mv_partner "id of exporter with whom importer has largest match value"
save "${temp_data}data_for_ranking_hs6_mv_partner.dta", replace

* Create ranking of match values in 2004 (base year)
use "${temp_data}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer newclid2
by hs6: gen mv_rank=_n
collapse (min) mv_rank, by(hs6_importer_id)
tostring mv_rank, generate(mv_rank_s)
gen hs6_importer_id_mv_rank= hs6_importer_id+"_"+mv_rank_s
drop mv_rank_s
gen hs6=substr(hs6_importer_id,1,6)
gsort hs6 mv_rank
by hs6: replace mv_rank=_n
label var mv_rank "ranking of match values in hs6 for importer"
drop hs6
sort hs6_importer_id_mv_rank
merge 1:1 hs6_importer_id_mv_rank using "${temp_data}data_for_ranking_hs6_mv_partner.dta"
keep if _merge==3
drop _merge hs6_importer_id_mv_rank
sort hs6_importer_id
save "${temp_data}importer_ranking_hs6.dta", replace


*************************
*Step 2 Create Mexican downgrading 6-digit level data
*************************

* Create data on export values between importers and exporters
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep rfc hs6 year newclid2 value_exporter_importer
collapse (sum) value_exporter_importer, by(rfc newclid2 year hs6)
gen hs6_exporter_id= hs6+"_"+rfc
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+importer_id
drop newclid2
save "${temp_data}exporter_importer_hs6_restrict.dta", replace

* Create data for rankings  
use "${temp_data}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_exporter_id_year= hs6_exporter_id+"_"+year_s
gsort hs_exporter_id_year -value_exporter_importer
by hs_exporter_id_year: gen importer_rank=_n
save "${temp_data}down_grading_hs6.dta", replace

* Identify main partner in 2004 2006 2007 2008
foreach j in 2004 2006 2007 2008{
use "${temp_data}down_grading_hs6.dta", clear 
keep if importer_rank==1 
keep if year==`j' 
keep hs6_exporter_id hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data}importer_ranking_hs6.dta"
keep if _merge==3
rename hs6_importer_id main`j'_id
rename mv_rank mv_rank`j'
keep hs6_exporter_id main`j'_id mv_rank`j' 
save "${temp_data}main`j'_hs6.dta", replace
}

* Create data on 2004-2006, 2004-2007, and 2004-2008 for Mexico (0406 and 0408 are for robustness checks)
foreach j in 6 7 8 {
* merge of rankings in 2004 and 200`j' 
use "${temp_data}down_grading_hs6.dta", clear 
keep if year==2004
keep hs6 rfc  hs6_exporter_id
duplicates drop
merge m:1 hs6_exporter_id using "${temp_data}main2004_hs6.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_exporter_id using "${temp_data}main200`j'_hs6.dta"
drop if _merge==2
drop _merge
sort hs6

* Identify whether the ranking was downgrading
sort hs6
gen mv_rank_d0`j'04=mv_rank200`j'-mv_rank2004
label var mv_rank_d0`j'04 "mv_rank_200`j'-mv_rank_2004"
gen i_mv_rank_d0`j'04=1 if mv_rank_d0`j'04>0
replace  i_mv_rank_d0`j'04=0 if mv_rank_d0`j'04<=0
replace  i_mv_rank_d0`j'04=. if mv_rank_d0`j'04==.

* Create data for main analysis (Mexico)
keep hs6 main2004_id hs6_exporter_id mv_rank_d0`j'04 mv_rank2004 mv_rank200`j' i_mv_rank_d0`j'04
gen newclid2=substr(main2004_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge
tostring hs6, replace
gen hs2=substr(hs6,1,2)

save "${output_data}mexico_for_analysis_04_0`j'_hs6_restrict.dta",replace
}


*************************
*Step 3 Create exporter rank by hs6
*************************

* Identify partner with highest match value
use "${temp_data}data_for_ranking_hs6.dta", replace
gsort hs6 -value_exporter_importer rfc 
by hs6: gen e_mv_rank=_n
keep hs6_importer_id hs6_exporter_id e_mv_rank 
tostring e_mv_rank, generate(e_mv_rank_s)
gen hs6_exporter_id_e_mv_rank= hs6_exporter_id+"_"+e_mv_rank_s
keep hs6_exporter_id hs6_exporter_id_e_mv_rank
sort hs6_exporter_id_e_mv_rank		
rename hs6_exporter_id e_mv_partner
label var e_mv_partner "id of exporter's main importer"
save "${temp_data}data_for_ranking_hs6_mv_partner_exp.dta", replace
		
* Create ranking of match values in 2004 (base year)
use "${temp_data}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer rfc
by hs6: gen e_mv_rank=_n
collapse (min) e_mv_rank, by(hs6_exporter_id)
tostring e_mv_rank, generate(e_mv_rank_s)
gen hs6_exporter_id_e_mv_rank= hs6_exporter_id+"_"+e_mv_rank_s
drop e_mv_rank_s
gen hs6=substr(hs6_exporter_id,1,6)
gsort hs6 e_mv_rank
by hs6: replace e_mv_rank=_n
label var e_mv_rank "ranking of match values in hs6 for exporter"
drop hs6	
merge 1:1 hs6_exporter_id_e_mv_rank using "${temp_data}data_for_ranking_hs6_mv_partner_exp.dta"
keep if _merge==3
drop _merge hs6_exporter_id_e_mv_rank
sort hs6_exporter_id
save "${temp_data}exporter_ranking_hs6.dta", replace


*************************
*Step 4 Create US upgrading 6-digit level restrict data
*************************

* Create data for rankings
use "${temp_data}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_importer_id_year= hs6_importer_id+"_"+year_s
gsort hs_importer_id_year -value_exporter_importer
by hs_importer_id_year: gen exporter_rank=_n
save "${temp_data}up_grading_hs6.dta", replace

* Identify main partner in 2004 2006 2007 2008
foreach j in 2004 2006 2007 2008{
use "${temp_data}up_grading_hs6.dta", clear
keep if exporter_rank==1
keep if year==`j'
keep hs6_exporter_id  hs6_importer_id
sort hs6_exporter_id
merge m:1 hs6_exporter_id using "${temp_data}exporter_ranking_hs6.dta"
drop if _merge!=3
rename hs6_exporter_id main`j'_id
rename e_mv_rank e_mv_rank`j'
keep hs6_importer_id main`j'_id  e_mv_rank`j'
save "${temp_data}main`j'_exp_hs6.dta", replace
}

* Create data on 2004-2006, 2004-2007, and 2004-2008 for US (0406 and 0408 are for robustness checks)
foreach j in 6 7 8 {
* Merge of rankings in 2004 and 200`j' 
use "${temp_data}up_grading_hs6.dta", clear 
keep if year==2004
keep hs6 importer_id  hs6_importer_id
duplicates drop
sort hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data}main2004_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6_importer_id
merge m:1 hs6_importer_id using "${temp_data}main200`j'_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6

* Identify whether the ranking was upgrading
sort hs6
gen e_mv_rank_d0`j'04= e_mv_rank200`j' -e_mv_rank2004
gen i_e_mv_rank_d0`j'04=1 if e_mv_rank_d0`j'04<0
replace  i_e_mv_rank_d0`j'04=0 if e_mv_rank_d0`j'04>=0
replace  i_e_mv_rank_d0`j'04=. if e_mv_rank_d0`j'04==.
label var e_mv_rank_d0`j'04 "e_mv_rank_200`j'-e_mv_rank_2004"
label var i_e_mv_rank_d0`j'04 "1 if e_mv_rank_d0`j'04<0 (upgrade) and 0 otherwise"

* Create data for main analysis (US)
keep hs6 main2004_id hs6_importer_id e_mv_rank_d0`j'04 i_e_mv_rank_d0`j'04 e_mv_rank2004 e_mv_rank200`j' 
gen newclid2=substr(hs6_importer_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge

gen hs2=substr(hs6,1,2)
save "${output_data}US_for_analysis_04_0`j'_hs6_restrict.dta", replace
}
